/*
select * from element join (
select scheme.* from scheme 
connect by prior scm_parent_scm_id = scm_id 
start with scm_id is null
) xx on xx.xy = element.elm_id

*/
select * from (
select elm_id,
min(elm_displayName) source_elm_name,
min(cat_minmultiplicity) min,
count(cmp_source_elm_id) min_real,
min (cat_name) cat_name,
min (scm_name) scm_name,

cat_id 

from element 
join  (
select sys_connect_by_path( scm_id, ',' ) path,scheme.scm_id,scheme.scm_name from scheme 
connect by prior scm_parent_scm_id = scm_id 
start with scm_id is not null
) scheme
on ','||element.elm_scm_id =  SUBSTR(path,0,length(element.elm_scm_id)+1)
join complexattribute on 
scheme.scm_id = cat_source_scm_id

left join composition on elm_id = cmp_source_elm_id and cat_id = cmp_cat_id
where cat_minmultiplicity > 0 and cat_relationtype = 'c'


group by elm_id,cat_id
) where min_real < min